package com.xmy.cultivate.mapper;

import com.xmy.cultivate.entity.Station;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 岗位表 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-01-31
 */
public interface StationMapper extends BaseMapper<Station> {

    @Select("select `name` from station where id = ${id} ")
    public String getStationNameForId(@Param("id") Long id);


    @Select({
            "WITH with_tb01 AS (",
            "    SELECT",
            "        t1.school_id,",
            "        t1.station_id,",
            "        t1.subjects_id,",
            "        t1.department_id,",
            "        t1.need_count,",
            "        t3.`name` AS department_name,",
            "        CONCAT(t2.name, IFNULL(t4.name, '')) AS station_needs_name,",
            "        (CASE WHEN t2.type = 1 THEN t1.school_id ELSE t1.department_id END) AS school_or_department_id,",
            "        ( CASE WHEN t2.type = 1 THEN t5.NAME ELSE t3.`name` END ) AS school_or_department_name",
            "    FROM",
            "        adms_school_needs AS t1",
            "        INNER JOIN adms_station AS t2 ON t1.station_id = t2.id",
            "        LEFT JOIN department AS t3 ON t1.department_id = t3.id",
            "        LEFT JOIN subjects AS t4 ON t1.subjects_id = t4.id",
            "        LEFT JOIN organization AS t5 ON t1.school_id = t5.id",
            "    WHERE",
            "        1 = 1",
            "        AND t1.status = 1",
            ")",
            "SELECT DISTINCT school_or_department_id, school_or_department_name",
            "FROM with_tb01",
            "GROUP BY school_or_department_id"
    })
    public List<Station> recruitmentName();


    @Select({"<script>",
            "select * from (select t1.school_id as schoolOrDepartmentId,t2.name as schoolOrDepartmentName from adms_allot_area_set as t1 INNER JOIN organization as t2 on t1.school_id = t2.id ORDER BY t1.sort asc) as t",
                    "UNION ALL",
                    "select * from (select department_id as schoolOrDepartmentId,t2.name as schoolOrDepartmentName from adms_station_needs as t1 INNER JOIN department as t2 on t1.department_id = t2.id where t1.`status` =1 GROUP BY t1.school_id,t1.department_id ORDER BY t1.sort asc) as t",
            "</script>"})
    public List<Station> getSchoolOrDepartList();


    @Select({"<script>",
            "SELECT",
                    "t1.id,",
                    "t1.NAME,",
                    "t1.is_has_subjects",
                    "FROM",
                    "adms_station AS t1",
                    "INNER JOIN adms_station_needs AS t2 ON t1.id = t2.station_id and t1.`status` = t2.`status`",
                    "WHERE",
                    "t1.`status` =1",
                    "<if test= 'type == 2'>",
                        "and ( t2.department_id = ${schoolIdOrDepartmentId} ) ",
                    "</if>",
                    "<if test= 'type == 1'>",
                        "and ( t2.department_id is null) ",
                    "</if>",
                    "GROUP BY",
                    "t1.id",
            "</script>"})
    public List<Station> getStationList(@Param("schoolIdOrDepartmentId") Long schoolIdOrDepartmentId,
                                        @Param("type") Integer type);

    @Select("select count(*) as count from adms_station_needs where department_id = ${departmentId}")
    public Integer stationType(@Param("departmentId") Long departmentId);
}
